Set weather data datetime

This notebook formats a date and a time column for weather data measurements with a unix timestamp. Each measurement is then inserted into a pumilio database.

Required packages

pandas
pyprind
pymilio

Variable declarations

weather_filepath – path to excel containing weather measurements, each with a unix timestamp



In [ ]:

    
weather_filepath = ""

Import statements



In [2]:

    
import pandas
import pyprind
from datetime import datetime
from Pymilio import database

Create and format a 'WeatherDate' and 'WeatherTime' column



In [2]:

    
weather_data = pandas.read_excel(weather_filepath)



In [3]:

    
weather_data['WeatherDate'] = weather_data['WeatherDate'].astype('str')
weather_data['WeatherTime'] = weather_data['WeatherTime'].astype('str')



In [4]:

    
for index, row in weather_data.iterrows():
    timestamp = row['timestamp']
    dt = datetime.fromtimestamp(timestamp)
    date = datetime.strftime(dt, "%Y-%m-%d")
    time = datetime.strftime(dt, "%H:%M:%S")
    
    weather_data.set_value(index, 'WeatherDate', date)
    weather_data.set_value(index, 'WeatherTime', time)



In [5]:

    
weather_data = weather_data.drop('timestamp', axis=1)
weather_data = weather_data.drop('LightIntensity', axis=1)

Connect to database



In [3]:

    
db = database.Pymilio_db_connection(user='pumilio',
                                    database='pumilio',
                                    read_default_file='~/.my.cnf.pumilio')

Insert weather measurements into a pumilio database



In [8]:

    
table_name = 'WeatherData'

column_list = [ n for n in weather_data.columns ]
column_names = ", ".join(column_list)

progress_bar = pyprind.ProgBar(len(weather_data), bar_char='█', title='Progress', monitor=True, stream=1, width=50)

for index, row in weather_data.iterrows():
    progress_bar.update(item_id=str(index))
    
    value_list = [ str(v) for v in row.as_matrix() ]
    value_strings = "'"
    value_strings = value_strings + "', '".join(value_list)
    value_strings = value_strings + "'"
    #value_strings = value_strings.replace('nan', 'NULL')

    statement = """INSERT INTO {0} ({1}) VALUES ({2})""".format(table_name, column_names, value_strings)

    db = pumilio_db._connect()
    c = db.cursor()
    c.execute(statement)
    c.close()
    db.close()









    



Progress
0%                                              100%
[██████████████████████████████████████████████████] | ETA: 00:00:00 | Item ID: 21883
Total time elapsed: 00:00:18

Optionally export dataframe to a csv file



In [73]:

    
#weather_data.to_csv("~/Desktop/weather_db.csv", index=False, header=False)